{
  "cells": [
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "view-in-github",
        "colab_type": "text"
      },
      "source": [
        "<a href=\"https://colab.research.google.com/github/ktynski/Marketing_Automations_Notebooks_With_GPT/blob/main/Automatic_Content_and_Keyword_Clustering_Descriptions_with_HuggingFace_Embeddings%2C_Agglomerative_Clustering_and_GPT_3_(Public).ipynb\" target=\"_parent\"><img src=\"https://colab.research.google.com/assets/colab-badge.svg\" alt=\"Open In Colab\"/></a>"
      ]
    },
    {
      "cell_type": "markdown",
      "source": [
        "http://www.Frac.tl - My Agency, we earn press and massively improve the ranking potential of our client's domains through a data-journalism style approach to earned media. We are excited about the limitless possibilities of AI enabled processes in Content Marketing, SEO, and PR. Email me at Kristin@frac.tl if you would like to learn more about our services."
      ],
      "metadata": {
        "id": "-Ipdve_OlcN7"
      }
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "o70meP0dUPC5"
      },
      "source": [
        "Step 1: Use ahrefs to download all the keywords you rank for in organic search."
      ]
    },
    {
      "cell_type": "markdown",
      "source": [
        "Step 2: Install the dependencies by pressing the play button on the cell below."
      ],
      "metadata": {
        "id": "wIf3_71bl99P"
      }
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "ENiUHbvaix8G"
      },
      "outputs": [],
      "source": [
        "!pip install openai\n",
        "!pip install transformers\n",
        "!pip install sentence-transformers"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "iJtn9JGOUxpz"
      },
      "source": [
        "\n",
        "Step 3: Upload the csv of your keywords from aherfs to the file navigator on the left."
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "lhukVWM5U87Y"
      },
      "source": [
        "Step 4: Load the csv into a dataframe by pressing the play button on the cell below."
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "import pandas as pd\n",
        "df = pd.read_csv(\"fractl.csv\",on_bad_lines='skip', encoding='utf-8')"
      ],
      "metadata": {
        "id": "lX8mQv9dyqtS"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "source": [
        "Step 5: Verify the dataframe looks good."
      ],
      "metadata": {
        "id": "5HYCFoeJm2Dx"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "df"
      ],
      "metadata": {
        "id": "fQaElx3_wEXP",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 424
        },
        "outputId": "1b4071d3-6288-4f49-9cae-b7b9207c4a49"
      },
      "execution_count": null,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "                                                Keyword  \\\n",
              "0                                            tangential   \n",
              "1                                      media bias chart   \n",
              "2                                        sponsored post   \n",
              "3                                             repurpose   \n",
              "4                                            repurposed   \n",
              "...                                                 ...   \n",
              "1579                 what generation is 1977 considered   \n",
              "1580                                graph of media bias   \n",
              "1581                                  news sources bias   \n",
              "1582  which social network is used by 68% of america...   \n",
              "1583                         types of jobs in marketing   \n",
              "\n",
              "                                          SERP features  Volume  KD   CPC  \\\n",
              "0           Knowledge card, People also ask, Image pack   44000  24  0.00   \n",
              "1                                             Sitelinks   31000  43  1.99   \n",
              "2           People also ask, Sitelinks, Knowledge panel   14000  38  8.44   \n",
              "3     Knowledge card, Sitelinks, People also ask, Im...   13000  49  2.00   \n",
              "4     Knowledge card, Local teaser, People also ask,...    9500  12  0.53   \n",
              "...                                                 ...     ...  ..   ...   \n",
              "1579       Featured snippet, People also ask, Sitelinks      10  56   NaN   \n",
              "1580                                                NaN      10  43   NaN   \n",
              "1581                         Sitelinks, People also ask       0  77  1.43   \n",
              "1582                         Sitelinks, People also ask       0  67   NaN   \n",
              "1583                         People also ask, Sitelinks       0  44  4.34   \n",
              "\n",
              "      Traffic  Current position  \\\n",
              "0           0                66   \n",
              "1           0                31   \n",
              "2           3                29   \n",
              "3           0                52   \n",
              "4           0                87   \n",
              "...       ...               ...   \n",
              "1579        0                46   \n",
              "1580        0                19   \n",
              "1581        0                41   \n",
              "1582        0                65   \n",
              "1583        0                68   \n",
              "\n",
              "                                            Current URL  Current URL inside  \\\n",
              "0               https://www.frac.tl/tangential-content/                 NaN   \n",
              "1     https://www.frac.tl/online-media-bias-and-accu...                 NaN   \n",
              "2     https://www.frac.tl/work/marketing-research/or...                 NaN   \n",
              "3     https://www.frac.tl/repurpose-successful-content/                 NaN   \n",
              "4     https://www.frac.tl/repurpose-successful-content/                 NaN   \n",
              "...                                                 ...                 ...   \n",
              "1579  https://www.frac.tl/work/marketing-research/co...                 NaN   \n",
              "1580  https://www.frac.tl/online-media-bias-and-accu...                 NaN   \n",
              "1581  https://www.frac.tl/online-media-bias-and-accu...                 NaN   \n",
              "1582  https://www.frac.tl/social-media-viral-marketing/                 NaN   \n",
              "1583  https://www.frac.tl/work/marketing-research/in...                 NaN   \n",
              "\n",
              "                  Updated  \n",
              "0     2023-02-01 07:31:49  \n",
              "1     2023-01-31 06:26:56  \n",
              "2     2023-02-01 00:54:56  \n",
              "3     2023-01-31 09:06:41  \n",
              "4     2023-01-30 12:08:26  \n",
              "...                   ...  \n",
              "1579  2023-01-20 18:45:10  \n",
              "1580  2023-01-24 18:11:10  \n",
              "1581  2023-01-26 22:49:49  \n",
              "1582  2023-01-10 14:20:11  \n",
              "1583  2023-02-01 06:24:43  \n",
              "\n",
              "[1584 rows x 10 columns]"
            ],
            "text/html": [
              "\n",
              "  <div id=\"df-fcdf006e-398e-412e-b40e-a6f1d727da77\">\n",
              "    <div class=\"colab-df-container\">\n",
              "      <div>\n",
              "<style scoped>\n",
              "    .dataframe tbody tr th:only-of-type {\n",
              "        vertical-align: middle;\n",
              "    }\n",
              "\n",
              "    .dataframe tbody tr th {\n",
              "        vertical-align: top;\n",
              "    }\n",
              "\n",
              "    .dataframe thead th {\n",
              "        text-align: right;\n",
              "    }\n",
              "</style>\n",
              "<table border=\"1\" class=\"dataframe\">\n",
              "  <thead>\n",
              "    <tr style=\"text-align: right;\">\n",
              "      <th></th>\n",
              "      <th>Keyword</th>\n",
              "      <th>SERP features</th>\n",
              "      <th>Volume</th>\n",
              "      <th>KD</th>\n",
              "      <th>CPC</th>\n",
              "      <th>Traffic</th>\n",
              "      <th>Current position</th>\n",
              "      <th>Current URL</th>\n",
              "      <th>Current URL inside</th>\n",
              "      <th>Updated</th>\n",
              "    </tr>\n",
              "  </thead>\n",
              "  <tbody>\n",
              "    <tr>\n",
              "      <th>0</th>\n",
              "      <td>tangential</td>\n",
              "      <td>Knowledge card, People also ask, Image pack</td>\n",
              "      <td>44000</td>\n",
              "      <td>24</td>\n",
              "      <td>0.00</td>\n",
              "      <td>0</td>\n",
              "      <td>66</td>\n",
              "      <td>https://www.frac.tl/tangential-content/</td>\n",
              "      <td>NaN</td>\n",
              "      <td>2023-02-01 07:31:49</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>1</th>\n",
              "      <td>media bias chart</td>\n",
              "      <td>Sitelinks</td>\n",
              "      <td>31000</td>\n",
              "      <td>43</td>\n",
              "      <td>1.99</td>\n",
              "      <td>0</td>\n",
              "      <td>31</td>\n",
              "      <td>https://www.frac.tl/online-media-bias-and-accu...</td>\n",
              "      <td>NaN</td>\n",
              "      <td>2023-01-31 06:26:56</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>2</th>\n",
              "      <td>sponsored post</td>\n",
              "      <td>People also ask, Sitelinks, Knowledge panel</td>\n",
              "      <td>14000</td>\n",
              "      <td>38</td>\n",
              "      <td>8.44</td>\n",
              "      <td>3</td>\n",
              "      <td>29</td>\n",
              "      <td>https://www.frac.tl/work/marketing-research/or...</td>\n",
              "      <td>NaN</td>\n",
              "      <td>2023-02-01 00:54:56</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>3</th>\n",
              "      <td>repurpose</td>\n",
              "      <td>Knowledge card, Sitelinks, People also ask, Im...</td>\n",
              "      <td>13000</td>\n",
              "      <td>49</td>\n",
              "      <td>2.00</td>\n",
              "      <td>0</td>\n",
              "      <td>52</td>\n",
              "      <td>https://www.frac.tl/repurpose-successful-content/</td>\n",
              "      <td>NaN</td>\n",
              "      <td>2023-01-31 09:06:41</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>4</th>\n",
              "      <td>repurposed</td>\n",
              "      <td>Knowledge card, Local teaser, People also ask,...</td>\n",
              "      <td>9500</td>\n",
              "      <td>12</td>\n",
              "      <td>0.53</td>\n",
              "      <td>0</td>\n",
              "      <td>87</td>\n",
              "      <td>https://www.frac.tl/repurpose-successful-content/</td>\n",
              "      <td>NaN</td>\n",
              "      <td>2023-01-30 12:08:26</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>...</th>\n",
              "      <td>...</td>\n",
              "      <td>...</td>\n",
              "      <td>...</td>\n",
              "      <td>...</td>\n",
              "      <td>...</td>\n",
              "      <td>...</td>\n",
              "      <td>...</td>\n",
              "      <td>...</td>\n",
              "      <td>...</td>\n",
              "      <td>...</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>1579</th>\n",
              "      <td>what generation is 1977 considered</td>\n",
              "      <td>Featured snippet, People also ask, Sitelinks</td>\n",
              "      <td>10</td>\n",
              "      <td>56</td>\n",
              "      <td>NaN</td>\n",
              "      <td>0</td>\n",
              "      <td>46</td>\n",
              "      <td>https://www.frac.tl/work/marketing-research/co...</td>\n",
              "      <td>NaN</td>\n",
              "      <td>2023-01-20 18:45:10</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>1580</th>\n",
              "      <td>graph of media bias</td>\n",
              "      <td>NaN</td>\n",
              "      <td>10</td>\n",
              "      <td>43</td>\n",
              "      <td>NaN</td>\n",
              "      <td>0</td>\n",
              "      <td>19</td>\n",
              "      <td>https://www.frac.tl/online-media-bias-and-accu...</td>\n",
              "      <td>NaN</td>\n",
              "      <td>2023-01-24 18:11:10</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>1581</th>\n",
              "      <td>news sources bias</td>\n",
              "      <td>Sitelinks, People also ask</td>\n",
              "      <td>0</td>\n",
              "      <td>77</td>\n",
              "      <td>1.43</td>\n",
              "      <td>0</td>\n",
              "      <td>41</td>\n",
              "      <td>https://www.frac.tl/online-media-bias-and-accu...</td>\n",
              "      <td>NaN</td>\n",
              "      <td>2023-01-26 22:49:49</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>1582</th>\n",
              "      <td>which social network is used by 68% of america...</td>\n",
              "      <td>Sitelinks, People also ask</td>\n",
              "      <td>0</td>\n",
              "      <td>67</td>\n",
              "      <td>NaN</td>\n",
              "      <td>0</td>\n",
              "      <td>65</td>\n",
              "      <td>https://www.frac.tl/social-media-viral-marketing/</td>\n",
              "      <td>NaN</td>\n",
              "      <td>2023-01-10 14:20:11</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>1583</th>\n",
              "      <td>types of jobs in marketing</td>\n",
              "      <td>People also ask, Sitelinks</td>\n",
              "      <td>0</td>\n",
              "      <td>44</td>\n",
              "      <td>4.34</td>\n",
              "      <td>0</td>\n",
              "      <td>68</td>\n",
              "      <td>https://www.frac.tl/work/marketing-research/in...</td>\n",
              "      <td>NaN</td>\n",
              "      <td>2023-02-01 06:24:43</td>\n",
              "    </tr>\n",
              "  </tbody>\n",
              "</table>\n",
              "<p>1584 rows × 10 columns</p>\n",
              "</div>\n",
              "      <button class=\"colab-df-convert\" onclick=\"convertToInteractive('df-fcdf006e-398e-412e-b40e-a6f1d727da77')\"\n",
              "              title=\"Convert this dataframe to an interactive table.\"\n",
              "              style=\"display:none;\">\n",
              "        \n",
              "  <svg xmlns=\"http://www.w3.org/2000/svg\" height=\"24px\"viewBox=\"0 0 24 24\"\n",
              "       width=\"24px\">\n",
              "    <path d=\"M0 0h24v24H0V0z\" fill=\"none\"/>\n",
              "    <path d=\"M18.56 5.44l.94 2.06.94-2.06 2.06-.94-2.06-.94-.94-2.06-.94 2.06-2.06.94zm-11 1L8.5 8.5l.94-2.06 2.06-.94-2.06-.94L8.5 2.5l-.94 2.06-2.06.94zm10 10l.94 2.06.94-2.06 2.06-.94-2.06-.94-.94-2.06-.94 2.06-2.06.94z\"/><path d=\"M17.41 7.96l-1.37-1.37c-.4-.4-.92-.59-1.43-.59-.52 0-1.04.2-1.43.59L10.3 9.45l-7.72 7.72c-.78.78-.78 2.05 0 2.83L4 21.41c.39.39.9.59 1.41.59.51 0 1.02-.2 1.41-.59l7.78-7.78 2.81-2.81c.8-.78.8-2.07 0-2.86zM5.41 20L4 18.59l7.72-7.72 1.47 1.35L5.41 20z\"/>\n",
              "  </svg>\n",
              "      </button>\n",
              "      \n",
              "  <style>\n",
              "    .colab-df-container {\n",
              "      display:flex;\n",
              "      flex-wrap:wrap;\n",
              "      gap: 12px;\n",
              "    }\n",
              "\n",
              "    .colab-df-convert {\n",
              "      background-color: #E8F0FE;\n",
              "      border: none;\n",
              "      border-radius: 50%;\n",
              "      cursor: pointer;\n",
              "      display: none;\n",
              "      fill: #1967D2;\n",
              "      height: 32px;\n",
              "      padding: 0 0 0 0;\n",
              "      width: 32px;\n",
              "    }\n",
              "\n",
              "    .colab-df-convert:hover {\n",
              "      background-color: #E2EBFA;\n",
              "      box-shadow: 0px 1px 2px rgba(60, 64, 67, 0.3), 0px 1px 3px 1px rgba(60, 64, 67, 0.15);\n",
              "      fill: #174EA6;\n",
              "    }\n",
              "\n",
              "    [theme=dark] .colab-df-convert {\n",
              "      background-color: #3B4455;\n",
              "      fill: #D2E3FC;\n",
              "    }\n",
              "\n",
              "    [theme=dark] .colab-df-convert:hover {\n",
              "      background-color: #434B5C;\n",
              "      box-shadow: 0px 1px 3px 1px rgba(0, 0, 0, 0.15);\n",
              "      filter: drop-shadow(0px 1px 2px rgba(0, 0, 0, 0.3));\n",
              "      fill: #FFFFFF;\n",
              "    }\n",
              "  </style>\n",
              "\n",
              "      <script>\n",
              "        const buttonEl =\n",
              "          document.querySelector('#df-fcdf006e-398e-412e-b40e-a6f1d727da77 button.colab-df-convert');\n",
              "        buttonEl.style.display =\n",
              "          google.colab.kernel.accessAllowed ? 'block' : 'none';\n",
              "\n",
              "        async function convertToInteractive(key) {\n",
              "          const element = document.querySelector('#df-fcdf006e-398e-412e-b40e-a6f1d727da77');\n",
              "          const dataTable =\n",
              "            await google.colab.kernel.invokeFunction('convertToInteractive',\n",
              "                                                     [key], {});\n",
              "          if (!dataTable) return;\n",
              "\n",
              "          const docLinkHtml = 'Like what you see? Visit the ' +\n",
              "            '<a target=\"_blank\" href=https://colab.research.google.com/notebooks/data_table.ipynb>data table notebook</a>'\n",
              "            + ' to learn more about interactive tables.';\n",
              "          element.innerHTML = '';\n",
              "          dataTable['output_type'] = 'display_data';\n",
              "          await google.colab.output.renderOutput(dataTable, element);\n",
              "          const docLink = document.createElement('div');\n",
              "          docLink.innerHTML = docLinkHtml;\n",
              "          element.appendChild(docLink);\n",
              "        }\n",
              "      </script>\n",
              "    </div>\n",
              "  </div>\n",
              "  "
            ]
          },
          "metadata": {},
          "execution_count": 14
        }
      ]
    },
    {
      "cell_type": "markdown",
      "source": [
        "Step 6: Use GPT3 to evaluate the keyword and url to predict the likely goal/intent of the searcher and add the prediction to the dataframe."
      ],
      "metadata": {
        "id": "NUN-Ci0oqhmW"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "import openai\n",
        "\n",
        "# Initialize the OpenAI API key\n",
        "openai.api_key = \"enter your OpenAI api key here\"\n",
        "\n",
        "def predict_goal_intent(url, keyword):\n",
        "    prompt = f\"You are an expert in search marketing and search behavior. You are particularly good at understanding and predicting the goals of a searcher. Please predict the most likely goal or search intent of the user visiting the URL '{url}' with the keyword '{keyword}. \\n The primary goal of this searcher is:'\"\n",
        "    response = openai.Completion.create(\n",
        "        engine=\"text-davinci-003\",\n",
        "        prompt=prompt,\n",
        "        max_tokens=1024,\n",
        "        n=1,\n",
        "        stop=None,\n",
        "        temperature=0.5,\n",
        "    ).get(\"choices\")[0].get(\"text\")\n",
        "\n",
        "    return response\n",
        "\n",
        "# Read the dataframe and use each row to generate the prompt and predict the goal/search intent\n",
        "import pandas as pd\n",
        "\n",
        "\n",
        "for index, row in df.iterrows():\n",
        "    url = row[\"Current URL\"]\n",
        "    keyword = row[\"Keyword\"]\n",
        "    goal_intent = predict_goal_intent(url, keyword)\n",
        "    df.at[index, \"GPT3 Response\"] = goal_intent\n",
        "    print(f\"URL: {url}, Keyword: {keyword}, Goal/Search Intent: {goal_intent}\")\n"
      ],
      "metadata": {
        "id": "kw0LFlu2Vzhf"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "source": [
        "Step 7: Check the dataframe and make sure everything looks good. Adjust the prompt if it isnt working for your needs."
      ],
      "metadata": {
        "id": "-0R5zASMqomG"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "df"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 424
        },
        "id": "olpS_YPbp-9a",
        "outputId": "88a3b527-6ceb-4cbf-a0a2-dc3725bb9ff0"
      },
      "execution_count": null,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "                                                Keyword  \\\n",
              "0                                            tangential   \n",
              "1                                      media bias chart   \n",
              "2                                        sponsored post   \n",
              "3                                             repurpose   \n",
              "4                                            repurposed   \n",
              "...                                                 ...   \n",
              "1579                 what generation is 1977 considered   \n",
              "1580                                graph of media bias   \n",
              "1581                                  news sources bias   \n",
              "1582  which social network is used by 68% of america...   \n",
              "1583                         types of jobs in marketing   \n",
              "\n",
              "                                          SERP features  Volume  KD   CPC  \\\n",
              "0           Knowledge card, People also ask, Image pack   44000  24  0.00   \n",
              "1                                             Sitelinks   31000  43  1.99   \n",
              "2           People also ask, Sitelinks, Knowledge panel   14000  38  8.44   \n",
              "3     Knowledge card, Sitelinks, People also ask, Im...   13000  49  2.00   \n",
              "4     Knowledge card, Local teaser, People also ask,...    9500  12  0.53   \n",
              "...                                                 ...     ...  ..   ...   \n",
              "1579       Featured snippet, People also ask, Sitelinks      10  56   NaN   \n",
              "1580                                                NaN      10  43   NaN   \n",
              "1581                         Sitelinks, People also ask       0  77  1.43   \n",
              "1582                         Sitelinks, People also ask       0  67   NaN   \n",
              "1583                         People also ask, Sitelinks       0  44  4.34   \n",
              "\n",
              "      Traffic  Current position  \\\n",
              "0           0                66   \n",
              "1           0                31   \n",
              "2           3                29   \n",
              "3           0                52   \n",
              "4           0                87   \n",
              "...       ...               ...   \n",
              "1579        0                46   \n",
              "1580        0                19   \n",
              "1581        0                41   \n",
              "1582        0                65   \n",
              "1583        0                68   \n",
              "\n",
              "                                            Current URL  Current URL inside  \\\n",
              "0               https://www.frac.tl/tangential-content/                 NaN   \n",
              "1     https://www.frac.tl/online-media-bias-and-accu...                 NaN   \n",
              "2     https://www.frac.tl/work/marketing-research/or...                 NaN   \n",
              "3     https://www.frac.tl/repurpose-successful-content/                 NaN   \n",
              "4     https://www.frac.tl/repurpose-successful-content/                 NaN   \n",
              "...                                                 ...                 ...   \n",
              "1579  https://www.frac.tl/work/marketing-research/co...                 NaN   \n",
              "1580  https://www.frac.tl/online-media-bias-and-accu...                 NaN   \n",
              "1581  https://www.frac.tl/online-media-bias-and-accu...                 NaN   \n",
              "1582  https://www.frac.tl/social-media-viral-marketing/                 NaN   \n",
              "1583  https://www.frac.tl/work/marketing-research/in...                 NaN   \n",
              "\n",
              "                  Updated                                      GPT3 Response  \\\n",
              "0     2023-02-01 07:31:49     to find information about tangential content.'   \n",
              "1     2023-01-31 06:26:56  To find a media bias chart that can be used to...   \n",
              "2     2023-02-01 00:54:56  To compare organic and sponsored posts on Inst...   \n",
              "3     2023-01-31 09:06:41  to find information on how to repurpose succes...   \n",
              "4     2023-01-30 12:08:26     to learn how to repurpose successful content'.   \n",
              "...                   ...                                                ...   \n",
              "1579  2023-01-20 18:45:10  To find out what generation the year 1977 is c...   \n",
              "1580  2023-01-24 18:11:10  to find a graph illustrating the media bias of...   \n",
              "1581  2023-01-26 22:49:49    To learn about news sources bias and accuracy'.   \n",
              "1582  2023-01-10 14:20:11  to find out which social network is used by 68...   \n",
              "1583  2023-02-01 06:24:43  To find out what types of jobs are available i...   \n",
              "\n",
              "      n_tokens  \n",
              "0            8  \n",
              "1            9  \n",
              "2           11  \n",
              "3           10  \n",
              "4           10  \n",
              "...        ...  \n",
              "1579         1  \n",
              "1580         1  \n",
              "1581         1  \n",
              "1582         1  \n",
              "1583         1  \n",
              "\n",
              "[1584 rows x 12 columns]"
            ],
            "text/html": [
              "\n",
              "  <div id=\"df-b8d8e967-0ae7-40d3-82a2-1a79962c7306\">\n",
              "    <div class=\"colab-df-container\">\n",
              "      <div>\n",
              "<style scoped>\n",
              "    .dataframe tbody tr th:only-of-type {\n",
              "        vertical-align: middle;\n",
              "    }\n",
              "\n",
              "    .dataframe tbody tr th {\n",
              "        vertical-align: top;\n",
              "    }\n",
              "\n",
              "    .dataframe thead th {\n",
              "        text-align: right;\n",
              "    }\n",
              "</style>\n",
              "<table border=\"1\" class=\"dataframe\">\n",
              "  <thead>\n",
              "    <tr style=\"text-align: right;\">\n",
              "      <th></th>\n",
              "      <th>Keyword</th>\n",
              "      <th>SERP features</th>\n",
              "      <th>Volume</th>\n",
              "      <th>KD</th>\n",
              "      <th>CPC</th>\n",
              "      <th>Traffic</th>\n",
              "      <th>Current position</th>\n",
              "      <th>Current URL</th>\n",
              "      <th>Current URL inside</th>\n",
              "      <th>Updated</th>\n",
              "      <th>GPT3 Response</th>\n",
              "      <th>n_tokens</th>\n",
              "    </tr>\n",
              "  </thead>\n",
              "  <tbody>\n",
              "    <tr>\n",
              "      <th>0</th>\n",
              "      <td>tangential</td>\n",
              "      <td>Knowledge card, People also ask, Image pack</td>\n",
              "      <td>44000</td>\n",
              "      <td>24</td>\n",
              "      <td>0.00</td>\n",
              "      <td>0</td>\n",
              "      <td>66</td>\n",
              "      <td>https://www.frac.tl/tangential-content/</td>\n",
              "      <td>NaN</td>\n",
              "      <td>2023-02-01 07:31:49</td>\n",
              "      <td>to find information about tangential content.'</td>\n",
              "      <td>8</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>1</th>\n",
              "      <td>media bias chart</td>\n",
              "      <td>Sitelinks</td>\n",
              "      <td>31000</td>\n",
              "      <td>43</td>\n",
              "      <td>1.99</td>\n",
              "      <td>0</td>\n",
              "      <td>31</td>\n",
              "      <td>https://www.frac.tl/online-media-bias-and-accu...</td>\n",
              "      <td>NaN</td>\n",
              "      <td>2023-01-31 06:26:56</td>\n",
              "      <td>To find a media bias chart that can be used to...</td>\n",
              "      <td>9</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>2</th>\n",
              "      <td>sponsored post</td>\n",
              "      <td>People also ask, Sitelinks, Knowledge panel</td>\n",
              "      <td>14000</td>\n",
              "      <td>38</td>\n",
              "      <td>8.44</td>\n",
              "      <td>3</td>\n",
              "      <td>29</td>\n",
              "      <td>https://www.frac.tl/work/marketing-research/or...</td>\n",
              "      <td>NaN</td>\n",
              "      <td>2023-02-01 00:54:56</td>\n",
              "      <td>To compare organic and sponsored posts on Inst...</td>\n",
              "      <td>11</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>3</th>\n",
              "      <td>repurpose</td>\n",
              "      <td>Knowledge card, Sitelinks, People also ask, Im...</td>\n",
              "      <td>13000</td>\n",
              "      <td>49</td>\n",
              "      <td>2.00</td>\n",
              "      <td>0</td>\n",
              "      <td>52</td>\n",
              "      <td>https://www.frac.tl/repurpose-successful-content/</td>\n",
              "      <td>NaN</td>\n",
              "      <td>2023-01-31 09:06:41</td>\n",
              "      <td>to find information on how to repurpose succes...</td>\n",
              "      <td>10</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>4</th>\n",
              "      <td>repurposed</td>\n",
              "      <td>Knowledge card, Local teaser, People also ask,...</td>\n",
              "      <td>9500</td>\n",
              "      <td>12</td>\n",
              "      <td>0.53</td>\n",
              "      <td>0</td>\n",
              "      <td>87</td>\n",
              "      <td>https://www.frac.tl/repurpose-successful-content/</td>\n",
              "      <td>NaN</td>\n",
              "      <td>2023-01-30 12:08:26</td>\n",
              "      <td>to learn how to repurpose successful content'.</td>\n",
              "      <td>10</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>...</th>\n",
              "      <td>...</td>\n",
              "      <td>...</td>\n",
              "      <td>...</td>\n",
              "      <td>...</td>\n",
              "      <td>...</td>\n",
              "      <td>...</td>\n",
              "      <td>...</td>\n",
              "      <td>...</td>\n",
              "      <td>...</td>\n",
              "      <td>...</td>\n",
              "      <td>...</td>\n",
              "      <td>...</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>1579</th>\n",
              "      <td>what generation is 1977 considered</td>\n",
              "      <td>Featured snippet, People also ask, Sitelinks</td>\n",
              "      <td>10</td>\n",
              "      <td>56</td>\n",
              "      <td>NaN</td>\n",
              "      <td>0</td>\n",
              "      <td>46</td>\n",
              "      <td>https://www.frac.tl/work/marketing-research/co...</td>\n",
              "      <td>NaN</td>\n",
              "      <td>2023-01-20 18:45:10</td>\n",
              "      <td>To find out what generation the year 1977 is c...</td>\n",
              "      <td>1</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>1580</th>\n",
              "      <td>graph of media bias</td>\n",
              "      <td>NaN</td>\n",
              "      <td>10</td>\n",
              "      <td>43</td>\n",
              "      <td>NaN</td>\n",
              "      <td>0</td>\n",
              "      <td>19</td>\n",
              "      <td>https://www.frac.tl/online-media-bias-and-accu...</td>\n",
              "      <td>NaN</td>\n",
              "      <td>2023-01-24 18:11:10</td>\n",
              "      <td>to find a graph illustrating the media bias of...</td>\n",
              "      <td>1</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>1581</th>\n",
              "      <td>news sources bias</td>\n",
              "      <td>Sitelinks, People also ask</td>\n",
              "      <td>0</td>\n",
              "      <td>77</td>\n",
              "      <td>1.43</td>\n",
              "      <td>0</td>\n",
              "      <td>41</td>\n",
              "      <td>https://www.frac.tl/online-media-bias-and-accu...</td>\n",
              "      <td>NaN</td>\n",
              "      <td>2023-01-26 22:49:49</td>\n",
              "      <td>To learn about news sources bias and accuracy'.</td>\n",
              "      <td>1</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>1582</th>\n",
              "      <td>which social network is used by 68% of america...</td>\n",
              "      <td>Sitelinks, People also ask</td>\n",
              "      <td>0</td>\n",
              "      <td>67</td>\n",
              "      <td>NaN</td>\n",
              "      <td>0</td>\n",
              "      <td>65</td>\n",
              "      <td>https://www.frac.tl/social-media-viral-marketing/</td>\n",
              "      <td>NaN</td>\n",
              "      <td>2023-01-10 14:20:11</td>\n",
              "      <td>to find out which social network is used by 68...</td>\n",
              "      <td>1</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>1583</th>\n",
              "      <td>types of jobs in marketing</td>\n",
              "      <td>People also ask, Sitelinks</td>\n",
              "      <td>0</td>\n",
              "      <td>44</td>\n",
              "      <td>4.34</td>\n",
              "      <td>0</td>\n",
              "      <td>68</td>\n",
              "      <td>https://www.frac.tl/work/marketing-research/in...</td>\n",
              "      <td>NaN</td>\n",
              "      <td>2023-02-01 06:24:43</td>\n",
              "      <td>To find out what types of jobs are available i...</td>\n",
              "      <td>1</td>\n",
              "    </tr>\n",
              "  </tbody>\n",
              "</table>\n",
              "<p>1584 rows × 12 columns</p>\n",
              "</div>\n",
              "      <button class=\"colab-df-convert\" onclick=\"convertToInteractive('df-b8d8e967-0ae7-40d3-82a2-1a79962c7306')\"\n",
              "              title=\"Convert this dataframe to an interactive table.\"\n",
              "              style=\"display:none;\">\n",
              "        \n",
              "  <svg xmlns=\"http://www.w3.org/2000/svg\" height=\"24px\"viewBox=\"0 0 24 24\"\n",
              "       width=\"24px\">\n",
              "    <path d=\"M0 0h24v24H0V0z\" fill=\"none\"/>\n",
              "    <path d=\"M18.56 5.44l.94 2.06.94-2.06 2.06-.94-2.06-.94-.94-2.06-.94 2.06-2.06.94zm-11 1L8.5 8.5l.94-2.06 2.06-.94-2.06-.94L8.5 2.5l-.94 2.06-2.06.94zm10 10l.94 2.06.94-2.06 2.06-.94-2.06-.94-.94-2.06-.94 2.06-2.06.94z\"/><path d=\"M17.41 7.96l-1.37-1.37c-.4-.4-.92-.59-1.43-.59-.52 0-1.04.2-1.43.59L10.3 9.45l-7.72 7.72c-.78.78-.78 2.05 0 2.83L4 21.41c.39.39.9.59 1.41.59.51 0 1.02-.2 1.41-.59l7.78-7.78 2.81-2.81c.8-.78.8-2.07 0-2.86zM5.41 20L4 18.59l7.72-7.72 1.47 1.35L5.41 20z\"/>\n",
              "  </svg>\n",
              "      </button>\n",
              "      \n",
              "  <style>\n",
              "    .colab-df-container {\n",
              "      display:flex;\n",
              "      flex-wrap:wrap;\n",
              "      gap: 12px;\n",
              "    }\n",
              "\n",
              "    .colab-df-convert {\n",
              "      background-color: #E8F0FE;\n",
              "      border: none;\n",
              "      border-radius: 50%;\n",
              "      cursor: pointer;\n",
              "      display: none;\n",
              "      fill: #1967D2;\n",
              "      height: 32px;\n",
              "      padding: 0 0 0 0;\n",
              "      width: 32px;\n",
              "    }\n",
              "\n",
              "    .colab-df-convert:hover {\n",
              "      background-color: #E2EBFA;\n",
              "      box-shadow: 0px 1px 2px rgba(60, 64, 67, 0.3), 0px 1px 3px 1px rgba(60, 64, 67, 0.15);\n",
              "      fill: #174EA6;\n",
              "    }\n",
              "\n",
              "    [theme=dark] .colab-df-convert {\n",
              "      background-color: #3B4455;\n",
              "      fill: #D2E3FC;\n",
              "    }\n",
              "\n",
              "    [theme=dark] .colab-df-convert:hover {\n",
              "      background-color: #434B5C;\n",
              "      box-shadow: 0px 1px 3px 1px rgba(0, 0, 0, 0.15);\n",
              "      filter: drop-shadow(0px 1px 2px rgba(0, 0, 0, 0.3));\n",
              "      fill: #FFFFFF;\n",
              "    }\n",
              "  </style>\n",
              "\n",
              "      <script>\n",
              "        const buttonEl =\n",
              "          document.querySelector('#df-b8d8e967-0ae7-40d3-82a2-1a79962c7306 button.colab-df-convert');\n",
              "        buttonEl.style.display =\n",
              "          google.colab.kernel.accessAllowed ? 'block' : 'none';\n",
              "\n",
              "        async function convertToInteractive(key) {\n",
              "          const element = document.querySelector('#df-b8d8e967-0ae7-40d3-82a2-1a79962c7306');\n",
              "          const dataTable =\n",
              "            await google.colab.kernel.invokeFunction('convertToInteractive',\n",
              "                                                     [key], {});\n",
              "          if (!dataTable) return;\n",
              "\n",
              "          const docLinkHtml = 'Like what you see? Visit the ' +\n",
              "            '<a target=\"_blank\" href=https://colab.research.google.com/notebooks/data_table.ipynb>data table notebook</a>'\n",
              "            + ' to learn more about interactive tables.';\n",
              "          element.innerHTML = '';\n",
              "          dataTable['output_type'] = 'display_data';\n",
              "          await google.colab.output.renderOutput(dataTable, element);\n",
              "          const docLink = document.createElement('div');\n",
              "          docLink.innerHTML = docLinkHtml;\n",
              "          element.appendChild(docLink);\n",
              "        }\n",
              "      </script>\n",
              "    </div>\n",
              "  </div>\n",
              "  "
            ]
          },
          "metadata": {},
          "execution_count": 34
        }
      ]
    },
    {
      "cell_type": "markdown",
      "source": [
        "Step 8 (optional): - Save the dataframe to csv"
      ],
      "metadata": {
        "id": "YvqgXYiKqex2"
      }
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "p037V9RCjmGN"
      },
      "outputs": [],
      "source": [
        "df.to_csv(\"GPT3_Responses_Added.csv\")"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "7YVyz10mVKEY"
      },
      "source": [
        "Step 9: Clean the dataframe so we can avoid errors in the next steps."
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "GWtvIcDgjm34"
      },
      "outputs": [],
      "source": [
        "import pandas as pd\n",
        "\n",
        "df['GPT3 Response'] = df['GPT3 Response'].str.strip()\n",
        "df['GPT3 Response'] = df['GPT3 Response'].astype(str)\n",
        "df.dropna(subset=['GPT3 Response'], inplace=True)\n"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "JXKnAv_kVR_Q"
      },
      "source": [
        "Step 10: Tokenize the data"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "7UsFn4dXilHq"
      },
      "outputs": [],
      "source": [
        "from transformers import GPT2TokenizerFast\n",
        "from sklearn.datasets import fetch_20newsgroups\n",
        "\n",
        "tokenizer = GPT2TokenizerFast.from_pretrained(\"gpt2\")\n",
        "df.dropna(subset=['GPT3 Response'], inplace=True)\n",
        "df['n_tokens'] = df['GPT3 Response'].apply(lambda x: len(tokenizer.encode(x)))\n"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "7L39m9lKxlsU"
      },
      "source": [
        "Step 11: Get free embeddings"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "DtkEbpLexzMb",
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "outputId": "c8efaa5a-35f9-4256-f4f8-f82b0fffc337"
      },
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "0          to find information about tangential content.'\n",
            "1       To find a media bias chart that can be used to...\n",
            "2       To compare organic and sponsored posts on Inst...\n",
            "3       to find information on how to repurpose succes...\n",
            "4          to learn how to repurpose successful content'.\n",
            "                              ...                        \n",
            "1579    To find out what generation the year 1977 is c...\n",
            "1580    to find a graph illustrating the media bias of...\n",
            "1581      To learn about news sources bias and accuracy'.\n",
            "1582    to find out which social network is used by 68...\n",
            "1583    To find out what types of jobs are available i...\n",
            "Name: GPT3 Response, Length: 1584, dtype: object\n"
          ]
        }
      ],
      "source": [
        "from sentence_transformers import SentenceTransformer\n",
        "from sklearn.cluster import AgglomerativeClustering\n",
        "import numpy as np\n",
        "\n",
        "embedder = SentenceTransformer('all-MiniLM-L6-v2')\n",
        "# Corpus with example sentences\n",
        "corpus = df['GPT3 Response']\n",
        "corpus_embeddings = \"\"\n",
        "print(corpus)\n",
        "\n",
        "def get_free_embeddings(thecorpus):\n",
        "  corpus_embeddings = embedder.encode(thecorpus)\n",
        "  # Normalize the embeddings to unit length\n",
        "  #corpus_embeddings = corpus_embeddings /  np.linalg.norm(corpus_embeddings, axis=1, keepdims=True)\n",
        "  return corpus_embeddings\n",
        "\n",
        "\n",
        "\n",
        "# Perform kmeans clustering\n",
        "\n",
        "df['open_similarity'] = df['GPT3 Response'].apply(lambda x: get_free_embeddings(x))\n",
        "clustering_model = AgglomerativeClustering(n_clusters=200) #, affinity='cosine', linkage='average', distance_threshold=0.4)\n",
        "corpus_embeddings = df['open_similarity'].to_list()\n",
        "clustering_model.fit(corpus_embeddings)\n",
        "df['cluster_assignment'] = clustering_model.labels_\n",
        "#embeddings file for Tensorflow Projector\n",
        "df.to_csv('crosswordembeddings_answeronly.csv', index=False)\n",
        "\n"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "doGxMeK2Vg-B"
      },
      "source": [
        "(Optional) Get OpenAI Embeddgings\n",
        "\n",
        "\n"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "C4NviphXkQBG"
      },
      "outputs": [],
      "source": [
        "import openai\n",
        "import numpy as np\n",
        "\n",
        "openai.api_key = \"enter your API key here\"\n",
        "\n",
        "def get_embedding(text, engine=\"text-similarity-davinci-003\"):\n",
        "   #text = text.replace(\"\\n\", \" \")\n",
        "   return openai.Embedding.create(input = [text], engine=engine)['data'][0]['embedding']\n",
        "\n",
        "df['davinci_similarity'] = df.description.apply(lambda x: get_embedding(x, engine='text-similarity-davinci-001'))\n",
        "df.to_csv('embeddings.csv', index=False)"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "2o8-Fmf2VnbR"
      },
      "source": [
        "Step 12: Save embeddings to Google Drive (or load previously created embedding) (Good practice because these files are very large and take a long time to upload/download through the colab interface)"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "8_jLkqVZoshX",
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "outputId": "8d3857ab-6a7f-4539-865e-31d769d72324"
      },
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "Mounted at /content/gdrive\n"
          ]
        }
      ],
      "source": [
        "from google.colab import drive\n",
        "drive.mount('/content/gdrive')\n",
        "!cp  /content/embeddings_big.csv /content/gdrive/MyDrive/\n"
      ]
    },
    {
      "cell_type": "markdown",
      "source": [
        "Step 13: Check dataframe and make sure everything looks ok."
      ],
      "metadata": {
        "id": "tuj0v4S2WpMg"
      }
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "upIQQzGiHgy3",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 424
        },
        "outputId": "5fe0ada9-640a-4918-eea3-6b0683fe45d6"
      },
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "                                                Keyword  \\\n",
              "0                                            tangential   \n",
              "1                                      media bias chart   \n",
              "2                                        sponsored post   \n",
              "3                                             repurpose   \n",
              "4                                            repurposed   \n",
              "...                                                 ...   \n",
              "1579                 what generation is 1977 considered   \n",
              "1580                                graph of media bias   \n",
              "1581                                  news sources bias   \n",
              "1582  which social network is used by 68% of america...   \n",
              "1583                         types of jobs in marketing   \n",
              "\n",
              "                                          SERP features  Volume  KD   CPC  \\\n",
              "0           Knowledge card, People also ask, Image pack   44000  24  0.00   \n",
              "1                                             Sitelinks   31000  43  1.99   \n",
              "2           People also ask, Sitelinks, Knowledge panel   14000  38  8.44   \n",
              "3     Knowledge card, Sitelinks, People also ask, Im...   13000  49  2.00   \n",
              "4     Knowledge card, Local teaser, People also ask,...    9500  12  0.53   \n",
              "...                                                 ...     ...  ..   ...   \n",
              "1579       Featured snippet, People also ask, Sitelinks      10  56   NaN   \n",
              "1580                                                NaN      10  43   NaN   \n",
              "1581                         Sitelinks, People also ask       0  77  1.43   \n",
              "1582                         Sitelinks, People also ask       0  67   NaN   \n",
              "1583                         People also ask, Sitelinks       0  44  4.34   \n",
              "\n",
              "      Traffic  Current position  \\\n",
              "0           0                66   \n",
              "1           0                31   \n",
              "2           3                29   \n",
              "3           0                52   \n",
              "4           0                87   \n",
              "...       ...               ...   \n",
              "1579        0                46   \n",
              "1580        0                19   \n",
              "1581        0                41   \n",
              "1582        0                65   \n",
              "1583        0                68   \n",
              "\n",
              "                                            Current URL  Current URL inside  \\\n",
              "0               https://www.frac.tl/tangential-content/                 NaN   \n",
              "1     https://www.frac.tl/online-media-bias-and-accu...                 NaN   \n",
              "2     https://www.frac.tl/work/marketing-research/or...                 NaN   \n",
              "3     https://www.frac.tl/repurpose-successful-content/                 NaN   \n",
              "4     https://www.frac.tl/repurpose-successful-content/                 NaN   \n",
              "...                                                 ...                 ...   \n",
              "1579  https://www.frac.tl/work/marketing-research/co...                 NaN   \n",
              "1580  https://www.frac.tl/online-media-bias-and-accu...                 NaN   \n",
              "1581  https://www.frac.tl/online-media-bias-and-accu...                 NaN   \n",
              "1582  https://www.frac.tl/social-media-viral-marketing/                 NaN   \n",
              "1583  https://www.frac.tl/work/marketing-research/in...                 NaN   \n",
              "\n",
              "                  Updated                                      GPT3 Response  \\\n",
              "0     2023-02-01 07:31:49     to find information about tangential content.'   \n",
              "1     2023-01-31 06:26:56  To find a media bias chart that can be used to...   \n",
              "2     2023-02-01 00:54:56  To compare organic and sponsored posts on Inst...   \n",
              "3     2023-01-31 09:06:41  to find information on how to repurpose succes...   \n",
              "4     2023-01-30 12:08:26     to learn how to repurpose successful content'.   \n",
              "...                   ...                                                ...   \n",
              "1579  2023-01-20 18:45:10  To find out what generation the year 1977 is c...   \n",
              "1580  2023-01-24 18:11:10  to find a graph illustrating the media bias of...   \n",
              "1581  2023-01-26 22:49:49    To learn about news sources bias and accuracy'.   \n",
              "1582  2023-01-10 14:20:11  to find out which social network is used by 68...   \n",
              "1583  2023-02-01 06:24:43  To find out what types of jobs are available i...   \n",
              "\n",
              "      n_tokens                                    open_similarity  \\\n",
              "0            8  [0.042889204, -0.013312144, -0.03489172, -0.00...   \n",
              "1            9  [0.07640689, -0.107262306, -0.080021106, -0.00...   \n",
              "2           11  [-0.032384124, 0.0017347123, 0.0022973663, -0....   \n",
              "3           10  [-0.030786913, -0.022316579, -0.04856662, 0.03...   \n",
              "4           10  [-0.027676435, -0.01025962, -0.024658661, 0.02...   \n",
              "...        ...                                                ...   \n",
              "1579         1  [0.0030951228, 0.089106716, -0.017612759, 0.00...   \n",
              "1580         1  [0.054504033, -0.057769705, -0.026010849, 0.06...   \n",
              "1581         1  [0.023444293, -0.016091963, -0.026164096, 0.08...   \n",
              "1582         1  [0.09377006, -0.040186718, -0.024217475, 0.081...   \n",
              "1583         1  [0.059109826, -0.07490915, -0.009178316, 0.040...   \n",
              "\n",
              "      cluster_assignment  \n",
              "0                    102  \n",
              "1                     34  \n",
              "2                    156  \n",
              "3                    106  \n",
              "4                    111  \n",
              "...                  ...  \n",
              "1579                  13  \n",
              "1580                 159  \n",
              "1581                 175  \n",
              "1582                 172  \n",
              "1583                  17  \n",
              "\n",
              "[1584 rows x 14 columns]"
            ],
            "text/html": [
              "\n",
              "  <div id=\"df-05b9bbed-33f8-4c35-b0d1-3a81259d87c0\">\n",
              "    <div class=\"colab-df-container\">\n",
              "      <div>\n",
              "<style scoped>\n",
              "    .dataframe tbody tr th:only-of-type {\n",
              "        vertical-align: middle;\n",
              "    }\n",
              "\n",
              "    .dataframe tbody tr th {\n",
              "        vertical-align: top;\n",
              "    }\n",
              "\n",
              "    .dataframe thead th {\n",
              "        text-align: right;\n",
              "    }\n",
              "</style>\n",
              "<table border=\"1\" class=\"dataframe\">\n",
              "  <thead>\n",
              "    <tr style=\"text-align: right;\">\n",
              "      <th></th>\n",
              "      <th>Keyword</th>\n",
              "      <th>SERP features</th>\n",
              "      <th>Volume</th>\n",
              "      <th>KD</th>\n",
              "      <th>CPC</th>\n",
              "      <th>Traffic</th>\n",
              "      <th>Current position</th>\n",
              "      <th>Current URL</th>\n",
              "      <th>Current URL inside</th>\n",
              "      <th>Updated</th>\n",
              "      <th>GPT3 Response</th>\n",
              "      <th>n_tokens</th>\n",
              "      <th>open_similarity</th>\n",
              "      <th>cluster_assignment</th>\n",
              "    </tr>\n",
              "  </thead>\n",
              "  <tbody>\n",
              "    <tr>\n",
              "      <th>0</th>\n",
              "      <td>tangential</td>\n",
              "      <td>Knowledge card, People also ask, Image pack</td>\n",
              "      <td>44000</td>\n",
              "      <td>24</td>\n",
              "      <td>0.00</td>\n",
              "      <td>0</td>\n",
              "      <td>66</td>\n",
              "      <td>https://www.frac.tl/tangential-content/</td>\n",
              "      <td>NaN</td>\n",
              "      <td>2023-02-01 07:31:49</td>\n",
              "      <td>to find information about tangential content.'</td>\n",
              "      <td>8</td>\n",
              "      <td>[0.042889204, -0.013312144, -0.03489172, -0.00...</td>\n",
              "      <td>102</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>1</th>\n",
              "      <td>media bias chart</td>\n",
              "      <td>Sitelinks</td>\n",
              "      <td>31000</td>\n",
              "      <td>43</td>\n",
              "      <td>1.99</td>\n",
              "      <td>0</td>\n",
              "      <td>31</td>\n",
              "      <td>https://www.frac.tl/online-media-bias-and-accu...</td>\n",
              "      <td>NaN</td>\n",
              "      <td>2023-01-31 06:26:56</td>\n",
              "      <td>To find a media bias chart that can be used to...</td>\n",
              "      <td>9</td>\n",
              "      <td>[0.07640689, -0.107262306, -0.080021106, -0.00...</td>\n",
              "      <td>34</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>2</th>\n",
              "      <td>sponsored post</td>\n",
              "      <td>People also ask, Sitelinks, Knowledge panel</td>\n",
              "      <td>14000</td>\n",
              "      <td>38</td>\n",
              "      <td>8.44</td>\n",
              "      <td>3</td>\n",
              "      <td>29</td>\n",
              "      <td>https://www.frac.tl/work/marketing-research/or...</td>\n",
              "      <td>NaN</td>\n",
              "      <td>2023-02-01 00:54:56</td>\n",
              "      <td>To compare organic and sponsored posts on Inst...</td>\n",
              "      <td>11</td>\n",
              "      <td>[-0.032384124, 0.0017347123, 0.0022973663, -0....</td>\n",
              "      <td>156</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>3</th>\n",
              "      <td>repurpose</td>\n",
              "      <td>Knowledge card, Sitelinks, People also ask, Im...</td>\n",
              "      <td>13000</td>\n",
              "      <td>49</td>\n",
              "      <td>2.00</td>\n",
              "      <td>0</td>\n",
              "      <td>52</td>\n",
              "      <td>https://www.frac.tl/repurpose-successful-content/</td>\n",
              "      <td>NaN</td>\n",
              "      <td>2023-01-31 09:06:41</td>\n",
              "      <td>to find information on how to repurpose succes...</td>\n",
              "      <td>10</td>\n",
              "      <td>[-0.030786913, -0.022316579, -0.04856662, 0.03...</td>\n",
              "      <td>106</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>4</th>\n",
              "      <td>repurposed</td>\n",
              "      <td>Knowledge card, Local teaser, People also ask,...</td>\n",
              "      <td>9500</td>\n",
              "      <td>12</td>\n",
              "      <td>0.53</td>\n",
              "      <td>0</td>\n",
              "      <td>87</td>\n",
              "      <td>https://www.frac.tl/repurpose-successful-content/</td>\n",
              "      <td>NaN</td>\n",
              "      <td>2023-01-30 12:08:26</td>\n",
              "      <td>to learn how to repurpose successful content'.</td>\n",
              "      <td>10</td>\n",
              "      <td>[-0.027676435, -0.01025962, -0.024658661, 0.02...</td>\n",
              "      <td>111</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>...</th>\n",
              "      <td>...</td>\n",
              "      <td>...</td>\n",
              "      <td>...</td>\n",
              "      <td>...</td>\n",
              "      <td>...</td>\n",
              "      <td>...</td>\n",
              "      <td>...</td>\n",
              "      <td>...</td>\n",
              "      <td>...</td>\n",
              "      <td>...</td>\n",
              "      <td>...</td>\n",
              "      <td>...</td>\n",
              "      <td>...</td>\n",
              "      <td>...</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>1579</th>\n",
              "      <td>what generation is 1977 considered</td>\n",
              "      <td>Featured snippet, People also ask, Sitelinks</td>\n",
              "      <td>10</td>\n",
              "      <td>56</td>\n",
              "      <td>NaN</td>\n",
              "      <td>0</td>\n",
              "      <td>46</td>\n",
              "      <td>https://www.frac.tl/work/marketing-research/co...</td>\n",
              "      <td>NaN</td>\n",
              "      <td>2023-01-20 18:45:10</td>\n",
              "      <td>To find out what generation the year 1977 is c...</td>\n",
              "      <td>1</td>\n",
              "      <td>[0.0030951228, 0.089106716, -0.017612759, 0.00...</td>\n",
              "      <td>13</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>1580</th>\n",
              "      <td>graph of media bias</td>\n",
              "      <td>NaN</td>\n",
              "      <td>10</td>\n",
              "      <td>43</td>\n",
              "      <td>NaN</td>\n",
              "      <td>0</td>\n",
              "      <td>19</td>\n",
              "      <td>https://www.frac.tl/online-media-bias-and-accu...</td>\n",
              "      <td>NaN</td>\n",
              "      <td>2023-01-24 18:11:10</td>\n",
              "      <td>to find a graph illustrating the media bias of...</td>\n",
              "      <td>1</td>\n",
              "      <td>[0.054504033, -0.057769705, -0.026010849, 0.06...</td>\n",
              "      <td>159</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>1581</th>\n",
              "      <td>news sources bias</td>\n",
              "      <td>Sitelinks, People also ask</td>\n",
              "      <td>0</td>\n",
              "      <td>77</td>\n",
              "      <td>1.43</td>\n",
              "      <td>0</td>\n",
              "      <td>41</td>\n",
              "      <td>https://www.frac.tl/online-media-bias-and-accu...</td>\n",
              "      <td>NaN</td>\n",
              "      <td>2023-01-26 22:49:49</td>\n",
              "      <td>To learn about news sources bias and accuracy'.</td>\n",
              "      <td>1</td>\n",
              "      <td>[0.023444293, -0.016091963, -0.026164096, 0.08...</td>\n",
              "      <td>175</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>1582</th>\n",
              "      <td>which social network is used by 68% of america...</td>\n",
              "      <td>Sitelinks, People also ask</td>\n",
              "      <td>0</td>\n",
              "      <td>67</td>\n",
              "      <td>NaN</td>\n",
              "      <td>0</td>\n",
              "      <td>65</td>\n",
              "      <td>https://www.frac.tl/social-media-viral-marketing/</td>\n",
              "      <td>NaN</td>\n",
              "      <td>2023-01-10 14:20:11</td>\n",
              "      <td>to find out which social network is used by 68...</td>\n",
              "      <td>1</td>\n",
              "      <td>[0.09377006, -0.040186718, -0.024217475, 0.081...</td>\n",
              "      <td>172</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>1583</th>\n",
              "      <td>types of jobs in marketing</td>\n",
              "      <td>People also ask, Sitelinks</td>\n",
              "      <td>0</td>\n",
              "      <td>44</td>\n",
              "      <td>4.34</td>\n",
              "      <td>0</td>\n",
              "      <td>68</td>\n",
              "      <td>https://www.frac.tl/work/marketing-research/in...</td>\n",
              "      <td>NaN</td>\n",
              "      <td>2023-02-01 06:24:43</td>\n",
              "      <td>To find out what types of jobs are available i...</td>\n",
              "      <td>1</td>\n",
              "      <td>[0.059109826, -0.07490915, -0.009178316, 0.040...</td>\n",
              "      <td>17</td>\n",
              "    </tr>\n",
              "  </tbody>\n",
              "</table>\n",
              "<p>1584 rows × 14 columns</p>\n",
              "</div>\n",
              "      <button class=\"colab-df-convert\" onclick=\"convertToInteractive('df-05b9bbed-33f8-4c35-b0d1-3a81259d87c0')\"\n",
              "              title=\"Convert this dataframe to an interactive table.\"\n",
              "              style=\"display:none;\">\n",
              "        \n",
              "  <svg xmlns=\"http://www.w3.org/2000/svg\" height=\"24px\"viewBox=\"0 0 24 24\"\n",
              "       width=\"24px\">\n",
              "    <path d=\"M0 0h24v24H0V0z\" fill=\"none\"/>\n",
              "    <path d=\"M18.56 5.44l.94 2.06.94-2.06 2.06-.94-2.06-.94-.94-2.06-.94 2.06-2.06.94zm-11 1L8.5 8.5l.94-2.06 2.06-.94-2.06-.94L8.5 2.5l-.94 2.06-2.06.94zm10 10l.94 2.06.94-2.06 2.06-.94-2.06-.94-.94-2.06-.94 2.06-2.06.94z\"/><path d=\"M17.41 7.96l-1.37-1.37c-.4-.4-.92-.59-1.43-.59-.52 0-1.04.2-1.43.59L10.3 9.45l-7.72 7.72c-.78.78-.78 2.05 0 2.83L4 21.41c.39.39.9.59 1.41.59.51 0 1.02-.2 1.41-.59l7.78-7.78 2.81-2.81c.8-.78.8-2.07 0-2.86zM5.41 20L4 18.59l7.72-7.72 1.47 1.35L5.41 20z\"/>\n",
              "  </svg>\n",
              "      </button>\n",
              "      \n",
              "  <style>\n",
              "    .colab-df-container {\n",
              "      display:flex;\n",
              "      flex-wrap:wrap;\n",
              "      gap: 12px;\n",
              "    }\n",
              "\n",
              "    .colab-df-convert {\n",
              "      background-color: #E8F0FE;\n",
              "      border: none;\n",
              "      border-radius: 50%;\n",
              "      cursor: pointer;\n",
              "      display: none;\n",
              "      fill: #1967D2;\n",
              "      height: 32px;\n",
              "      padding: 0 0 0 0;\n",
              "      width: 32px;\n",
              "    }\n",
              "\n",
              "    .colab-df-convert:hover {\n",
              "      background-color: #E2EBFA;\n",
              "      box-shadow: 0px 1px 2px rgba(60, 64, 67, 0.3), 0px 1px 3px 1px rgba(60, 64, 67, 0.15);\n",
              "      fill: #174EA6;\n",
              "    }\n",
              "\n",
              "    [theme=dark] .colab-df-convert {\n",
              "      background-color: #3B4455;\n",
              "      fill: #D2E3FC;\n",
              "    }\n",
              "\n",
              "    [theme=dark] .colab-df-convert:hover {\n",
              "      background-color: #434B5C;\n",
              "      box-shadow: 0px 1px 3px 1px rgba(0, 0, 0, 0.15);\n",
              "      filter: drop-shadow(0px 1px 2px rgba(0, 0, 0, 0.3));\n",
              "      fill: #FFFFFF;\n",
              "    }\n",
              "  </style>\n",
              "\n",
              "      <script>\n",
              "        const buttonEl =\n",
              "          document.querySelector('#df-05b9bbed-33f8-4c35-b0d1-3a81259d87c0 button.colab-df-convert');\n",
              "        buttonEl.style.display =\n",
              "          google.colab.kernel.accessAllowed ? 'block' : 'none';\n",
              "\n",
              "        async function convertToInteractive(key) {\n",
              "          const element = document.querySelector('#df-05b9bbed-33f8-4c35-b0d1-3a81259d87c0');\n",
              "          const dataTable =\n",
              "            await google.colab.kernel.invokeFunction('convertToInteractive',\n",
              "                                                     [key], {});\n",
              "          if (!dataTable) return;\n",
              "\n",
              "          const docLinkHtml = 'Like what you see? Visit the ' +\n",
              "            '<a target=\"_blank\" href=https://colab.research.google.com/notebooks/data_table.ipynb>data table notebook</a>'\n",
              "            + ' to learn more about interactive tables.';\n",
              "          element.innerHTML = '';\n",
              "          dataTable['output_type'] = 'display_data';\n",
              "          await google.colab.output.renderOutput(dataTable, element);\n",
              "          const docLink = document.createElement('div');\n",
              "          docLink.innerHTML = docLinkHtml;\n",
              "          element.appendChild(docLink);\n",
              "        }\n",
              "      </script>\n",
              "    </div>\n",
              "  </div>\n",
              "  "
            ]
          },
          "metadata": {},
          "execution_count": 37
        }
      ],
      "source": [
        "df"
      ]
    },
    {
      "cell_type": "markdown",
      "source": [
        "Step 14: Have GPT3 evaluate the embeddings and generate natural language descriptions of what the embeddings are \"mostly about.\" Add these to the dataframe."
      ],
      "metadata": {
        "id": "8VvSJZ65WwZv"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "import pandas as pd\n",
        "import random\n",
        "import openai\n",
        "\n",
        "# Initialize the OpenAI API key\n",
        "openai.api_key = \"Enter your API key here\"\n",
        "\n",
        "\n",
        "\n",
        "def evaluate_cluster(cluster_data):\n",
        "    # Concatenate the text from the sample data into a single string\n",
        "    text = \"\\n\".join(cluster_data['GPT3 Response'].tolist())\n",
        "\n",
        "    # Use the GPT-3 API to generate a description of the cluster\n",
        "    model_engine = \"text-davinci-003\"\n",
        "    prompt = f\"What is the general description of this cluster of texts?\\n\\n{text}\"\n",
        "    completions = openai.Completion.create(\n",
        "        engine=model_engine,\n",
        "        prompt=prompt,\n",
        "        max_tokens=1024,\n",
        "        n=1,\n",
        "        stop=None,\n",
        "        temperature=0.5,\n",
        "        presence_penalty=0,\n",
        "    )\n",
        "\n",
        "    message = completions.choices[0].text\n",
        "    print(message)\n",
        "    return message\n",
        "\n",
        "# Load the dataframe\n",
        "df = df\n",
        "\n",
        "# Get unique cluster numbers\n",
        "unique_clusters = df['cluster_assignment'].unique()\n",
        "\n",
        "# Create a dictionary to store the cluster descriptions\n",
        "cluster_descriptions = {}\n",
        "\n",
        "# Loop through each unique cluster number\n",
        "for cluster in unique_clusters:\n",
        "    # Get a sample of the cluster\n",
        "    if len(df[df['cluster_assignment'] == cluster]) > 8:\n",
        "      sample = df[df['cluster_assignment'] == cluster].sample(8)\n",
        "    else:\n",
        "      sample = df[df['cluster_assignment'] == cluster]\n",
        "\n",
        "    # Evaluate the sample with GPT-3\n",
        "    cluster_description = evaluate_cluster(sample)\n",
        "\n",
        "    # Add the description to the dictionary\n",
        "    cluster_descriptions[cluster] = cluster_description\n",
        "\n",
        "# Add the descriptions to the dataframe\n",
        "df['Cluster Description'] = df['cluster_assignment'].map(cluster_descriptions)\n",
        "\n",
        "# Save the updated dataframe\n",
        "df.to_csv('data_with_descriptions.csv', index=False)\n"
      ],
      "metadata": {
        "id": "aDHZwfTzzMId"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "source": [
        "df"
      ],
      "metadata": {
        "id": "HQA0XDnt4QRJ"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "source": [
        "Step 15: Generate Embeddings and Metadata files for visualization in Google's Tensorboard Embeddings Projector. (This works, but something is wrong with the metadata labeling, I will try and fix this later, sorry!) https://projector.tensorflow.org/"
      ],
      "metadata": {
        "id": "6Xom4O1NXFVE"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "import pandas as pd\n",
        "import matplotlib.pyplot as plt\n",
        "from sklearn.manifold import TSNE\n",
        "import numpy as np\n",
        "import ast\n",
        "import pandas as pd\n",
        "import numpy as np\n",
        "from sklearn.manifold import TSNE\n",
        "from sklearn import preprocessing\n",
        "import tensorflow as tf\n",
        "\n",
        "# Load your data into a pandas dataframe\n",
        "df = pd.read_csv(\"/content/data_with_descriptions.csv\")\n",
        "\n",
        "\n",
        "# Encode the string data using LabelEncoder\n",
        "le = preprocessing.LabelEncoder()\n",
        "df['open_similarity'] = le.fit_transform(df['open_similarity'].astype(str))\n",
        "df['Cluster Description'] = le.fit_transform(df['Cluster Description'].astype(str))\n",
        "\n",
        "# Prepare the data for tsne\n",
        "X = df[['Volume', 'cluster_assignment', 'Cluster Description']]\n",
        "y = df['open_similarity']\n",
        "\n",
        "# Fit the data to the tsne model\n",
        "tsne = TSNE(n_components=2)\n",
        "tsne_result = tsne.fit_transform(X)\n",
        "\n",
        "# Create the two files needed for the TensorFlow Embeddings Projector\n",
        "# Create the metadata file\n",
        "metadata = pd.DataFrame(columns=['cluster_assignment', 'Cluster Description', 'open_similarity', 'x', 'y'])\n",
        "\n",
        "# Create the embeddings file\n",
        "embeddings = pd.DataFrame(data={'x': tsne_result[:, 0], 'y': tsne_result[:, 1]})\n",
        "\n",
        "# Loop through the dataframe and add the corresponding metadata for each row in the embeddings file\n",
        "for index, row in X.iterrows():\n",
        "    metadata = metadata.append({'cluster_assignment': row['cluster_assignment'], 'Cluster Description': row['Cluster Description'], 'open_similarity': y[index], 'x': tsne_result[index, 0], 'y': tsne_result[index, 1]}, ignore_index=True)\n",
        "\n",
        "# Save the files\n",
        "metadata.to_csv('metadata.tsv', sep='\\t', index=False, header=True)\n",
        "embeddings.to_csv('embeddings.tsv', sep='\\t', index=False, header=False)"
      ],
      "metadata": {
        "id": "YXMhSEDv6B2u"
      },
      "execution_count": null,
      "outputs": []
    }
  ],
  "metadata": {
    "colab": {
      "machine_shape": "hm",
      "provenance": [],
      "include_colab_link": true
    },
    "kernelspec": {
      "display_name": "Python 3",
      "name": "python3"
    },
    "language_info": {
      "name": "python"
    }
  },
  "nbformat": 4,
  "nbformat_minor": 0
}